package net.risesoft.risebi.collection.web.admin;

import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.risesoft.risebi.commons.util.sqlfilter.JsonFilterResolver;
import net.risesoft.risebi.collection.model.Application;
import net.risesoft.risebi.collection.model.Catalog;
import net.risesoft.risebi.collection.model.DbColumn;
import net.risesoft.risebi.collection.model.Query;
import net.risesoft.risebi.collection.model.QueryParameter;
import net.risesoft.risebi.collection.model.SoCollection;
import net.risesoft.risebi.collection.service.ApplicationManager;
import net.risesoft.risebi.collection.service.CatalogManager;
import net.risesoft.risebi.collection.service.DbMetaDataManager;
import net.risesoft.risebi.collection.service.QueryManager;
import net.risesoft.risebi.collection.service.QueryParameterManager;
import net.risesoft.risebi.collection.web.BaseAction;
import net.risesoft.risebi.commons.util.GUID;
import net.risesoft.risebi.commons.util.JSONUtil;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.beanutils.LazyDynaBean;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.struts2.convention.annotation.Action;
import org.apache.struts2.convention.annotation.Namespace;
import org.apache.struts2.convention.annotation.Result;

/**
*
* @author <a href="mailto: dingzhaojun2046@hotmail.com">丁兆俊</a>
* @version $Revision$
* 
*/
@Namespace("/admin/QueryAction")
public class QueryAction extends BaseAction {
	private static final long serialVersionUID = 4373651192038148229L;
	private static Log log = LogFactory.getLog(QueryAction.class);
	
	private Query query;	
	private QueryManager queryManager;
	private QueryParameterManager queryParameterManager;
	private ApplicationManager applicationManager;
	private CatalogManager catalogManager;
	private DbMetaDataManager dbMetaDataManager;
	
	private String field; //Ext.ux.plugins.RemoteValidator
	private String value; //Ext.ux.plugins.RemoteValidator
	private String crud;
	private String deletedQuery;
	private String jsonQueryParams;
	
	private String serviceGuid;
	private String applicationGuid;
	private String applicationName;
	private String tableName;

	@Action(value="prepareList",
			results={@Result(name="success", location="/admin/query/queryList.jsp")}
	)
	public String prepareList() throws Exception {
		Application app = (Application)applicationManager.getObject(Application.class, this.applicationGuid);
		this.applicationName = app.getChineseName();

		return SUCCESS;
	}

	@Action(value="list")
	public String list() throws Exception {
		try{
			if(this.query==null){
				this.query = new Query();
			}
			
			Map map = queryManager.getPagedByAppOrderByTabIndex(this.query, this.start, this.limit);
			this.json = JSONUtil.getJsonString("include","count,list,guid,name,type,beNative,tabIndex",map);
		}catch(Exception e){
			this.json = "[]";
		}
		
		return AJAX;
	}
	
	public String prepareAdd() throws Exception {
				
		return PREPAREADD;
	}

	@Action(value="add")
	public String add() throws Exception {
		LazyDynaBean ldb = new  LazyDynaBean();
		try{
			Double count = (Double)queryManager.getUniqueObject("select max(q.tabIndex) from Query q");
			if(count==null){
				count = new Double(0);
			}
			
			String guid = new GUID().toString();
			query.setGuid(guid);
			query.setTabIndex(count.intValue()+1.0);
			
			queryManager.saveObject(query);		
			
			if(this.jsonQueryParams.trim().length()>0){
				QueryParameter[] ps = (QueryParameter[])JSONUtil.stringToArray(this.jsonQueryParams, QueryParameter.class);
				for (QueryParameter p : ps){
					if(p.getGuid()==null || p.getGuid().length()==0){
						p.setGuid(new GUID().toString());
					}
					if(p.getQuery()==null){
						//p.setQuery(query);
						p.setQueryGuid(guid);
					}
					queryParameterManager.saveObject(p);
				}
			}
			
			ldb.set("success", true);
			ldb.set("reason", "增加查询成功!");
			
			ldb.set("guid", guid);
			ldb.set("name", query.getName());
			ldb.set("type", query.getType());
			ldb.set("beNative", query.getBeNative());
			ldb.set("tabIndex", query.getTabIndex());
			
			this.json = JSONObject.fromObject(ldb).toString();
		}catch(Exception e){
			ldb.set("success", false);
			ldb.set("reason", e.getMessage());
			this.json = JSONObject.fromObject(ldb).toString();
			e.printStackTrace();
		}
		
		return AJAX;
	}

	@Action(value="prepareQueryParams",
			results={@Result(name="success", location="/admin/query/queryParams.jsp")}
	)
	public String prepareQueryParams() throws Exception {
		return SUCCESS;
	}

	@Action(value="prepareEdit",
			results={@Result(name="success", location="/admin/query/queryEdit.jsp")}
	)
	public String prepareEdit() throws Exception {
		if(query.getGuid()==null || query.getGuid().trim().length()==0){
			query = new Query();
			query.setAppGuid(this.applicationGuid);
		}else{
			query = (Query)queryManager.getObject(Query.class, query.getGuid());
		}
		
		if(query.getBeNative()==null){
			query.setBeNative(false);
		}
		if(query.getTabIndex()==null){
			query.setTabIndex(100.0);
		}
		
		String content = query.getContent();
		if(content==null){
			query.setContent("from dyna_so_a");
		}else{
			//query.setContent(content.replace("'", "\\'").replace("\"", "\\\""));
		}
		
		String tableNames = "";
		List solist = catalogManager.getCatalogWithSoByApp(query.getAppGuid(),true);
		for(Object o : solist){
			Catalog so = (Catalog)o;
			if(!"d".equalsIgnoreCase(so.getSoType().getCode()) ){
				String tableName = so.getTableName().trim().toLowerCase();
				String soTypeName = tableName + "  【" + so.getName() + "】";
				if(tableNames.length()==0){
					tableNames = "['" + tableName + "','" + soTypeName + "']";
				}else{
					tableNames += ",['" + tableName + "','" + soTypeName + "']";
				}
			}
			
		}
		
		this.json = "[" + tableNames + "]";			
		return SUCCESS;
	}

	@Action(value="edit")
	public String edit() throws Exception {
		LazyDynaBean ldb = new  LazyDynaBean();
		try{
			String guid = new GUID().toString();
			if(query.getBeNative()==null){
				query.setBeNative(false);
			}
			if(query.getTabIndex()==null){
				query.setTabIndex(100.0);
			}
			if(query.getGuid()==null || query.getGuid().trim().length()==0){
				query.setGuid(guid);
			}

			queryManager.saveObject(query);	
			
			queryParameterManager.execute("delete from QueryParameter p where p.query.guid='" + query.getGuid() + "'");
			if(this.jsonQueryParams.trim().length()>0){
				QueryParameter[] ps = (QueryParameter[])JSONUtil.stringToArray(this.jsonQueryParams, QueryParameter.class);
				for (QueryParameter p : ps){
					if(p.getGuid()==null || p.getGuid().length()==0){
						p.setGuid(new GUID().toString());
					}
					if(p.getQuery()==null){
						//p.setQuery(query);
						p.setQueryGuid(query.getGuid());
					}
					queryParameterManager.saveObject(p);
				}
			}

			ldb.set("success", true);
			ldb.set("reason", "保存成功!");
			
			ldb.set("guid", query.getGuid());
			ldb.set("name", query.getName());
			ldb.set("type", query.getType());
			ldb.set("beNative", query.getBeNative());
			ldb.set("tabIndex", query.getTabIndex());
			
			this.json = JSONObject.fromObject(ldb).toString();
		}catch(Exception e){
			ldb.set("success", false);
			ldb.set("reason", e.getMessage());
			this.json = JSONObject.fromObject(ldb).toString();
			e.printStackTrace();
		}

		return AJAX;
	}
	
	@Action(value="remove")
	public String remove() throws Exception {
		LazyDynaBean ldb = new  LazyDynaBean();
		try{
			//query = (Query)queryManager.getObject(Query.class, query.getGuid());
			//queryParameterManager.removeObjects(query.getParams());
			queryParameterManager.execute("delete from QueryParameter t where t.query.guid='" + query.getGuid() + "'");
			queryManager.removeObject(Query.class, query.getGuid()); 		
			
			ldb.set("success", true);
			ldb.set("reason", "删除查询成功!");
			this.json = JSONObject.fromObject(ldb).toString();
		}catch(Exception e){
			ldb.set("success", false);
			ldb.set("reason", e.getMessage());
			this.json = JSONObject.fromObject(ldb).toString();
			e.printStackTrace();
		}

		return AJAX;
	}
	
	@Action(value="validateQueryName")
	public String validateQueryName() throws Exception {
		String guid = this.query.getGuid();
		
		LazyDynaBean ldb = new  LazyDynaBean();		
		if(guid==null || guid.trim().length()==0){
			Integer count = queryManager.getCount("select count(*) from Query t where t.name='" + this.value.trim() + "' and t.application.guid ='" + this.query.getAppGuid() + "'");
			if(count>0){
				ldb.set("success", true);
				ldb.set("valid", false);
				ldb.set("reason", "名称重复。");
			}else{
				ldb.set("success", true);
				ldb.set("valid", true);
				ldb.set("reason", "名称不重复，可以使用。");
			}
		}else{
			Integer count = queryManager.getCount("select count(*) from Query t where t.name='" + this.value.trim() + "' and t.guid !='" + guid + "' and t.application.guid ='" + this.query.getAppGuid() + "'");
			if(count>0){
				ldb.set("success", true);
				ldb.set("valid", false);
				ldb.set("reason", "名称重复。");
			}else{
				ldb.set("success", true);
				ldb.set("valid", true);
				ldb.set("reason", "名称不重复，可以使用。");
			}
		}
		
		this.json = JSONObject.fromObject(ldb).toString();	
		return AJAX;
	}	

	@Action(value="validateField")
	public String validateField() throws Exception {
		LazyDynaBean ldb = new  LazyDynaBean();		
		try{
			if("query.name".equals(this.field)){
				List lst = queryManager.find("from Query q where q.name = '" + this.value + "'");
				if("c".equals(this.crud)){
					if(lst.size()>0){
						ldb.set("success", true);
						ldb.set("valid", false);
						ldb.set("reason", "query的名称重复。");
						this.json = JSONObject.fromObject(ldb).toString();
						
						return "validateField";
					}
				}else{
					if(lst.size()>0){
						Query q = (Query)lst.get(0);
						if(!q.getGuid().equalsIgnoreCase(query.getGuid())){
							ldb.set("success", true);
							ldb.set("valid", false);
							ldb.set("reason", "query的名称重复。");
							this.json = JSONObject.fromObject(ldb).toString();
							
							return "validateField";						
						}
					}
				}			
			}
			ldb.set("success", true);
			ldb.set("valid", true);
			ldb.set("reason", "validateField success");
			this.json = JSONObject.fromObject(ldb).toString();			
		}catch(Exception e){
			ldb.set("success", true);
			ldb.set("valid", false);
			ldb.set("reason", e.getMessage());
			this.json = JSONObject.fromObject(ldb).toString();			
		}
		
		return AJAX;		
	}

	@Action(value="refreshJson")
	public String refreshJson() throws Exception {
		try{
			if("jsonQuerys".equalsIgnoreCase(this.whichJson)){
				List lst = queryManager.getObjects(Query.class);
				this.json = JSONUtil.getJsonString("include", "guid,name", lst);					
			}else if("jsonAllQuerys".equalsIgnoreCase(this.whichJson)){
				List lst = queryManager.getObjects(Query.class);
				//this.json = JSONUtil.getJsonString("exclude", "params", lst);
				this.json = JSONUtil.getJsonString("include", "guid,name,type,beNative,tabIndex", lst);
			}else if("jsonQueryParams".equalsIgnoreCase(this.whichJson)){
				String queryGuid = query.getGuid();
				if(queryGuid==null || queryGuid.trim().length()==0){
					this.json = "[]";
				}else{
					query = (Query)queryManager.getObject(Query.class, queryGuid);
					List lst = query.getParams();
					this.json = JSONUtil.getJsonString("include", "guid,name,type,arrayed,tabIndex", lst);
				}
			}else if("jsonFieldConfig".equalsIgnoreCase(this.whichJson)){
				StringBuilder fieldConfig = new StringBuilder();				
				List<DbColumn> lst = dbMetaDataManager.listAllColumns(this.tableName);
				for(DbColumn d : lst){
					String column_name = d.getColumn_name().trim().toLowerCase();
					String column_caption = d.getComment().trim().toLowerCase();
					int column_type = d.getData_type();
					
					if(column_name.endsWith("guid")){
						continue;
					}
					
					switch(column_type){
					//整型
					case Types.TINYINT:
					case Types.SMALLINT:
					case Types.INTEGER:
					case Types.BIGINT:
						fieldConfig.append("{id: '" + column_name + "', label: '" + column_caption + "', type: 'int'},");
						break;
					//浮点型
					case Types.FLOAT:
					case Types.REAL:
					case Types.DOUBLE:
					case Types.NUMERIC:
					case Types.DECIMAL:
						fieldConfig.append("{id: '" + column_name + "', label: '" + column_caption + "', type: 'float'},");
						break;
					//字符串
					case Types.CHAR:
					case Types.VARCHAR:
//						case Types.NCHAR:
//						case Types.NVARCHAR:
						fieldConfig.append("{id: '" + column_name + "', label: '" + column_caption + "', type: 'string'},");
						break;
					case Types.LONGVARCHAR:
//						case Types.LONGNVARCHAR:
						break;
					//BOOLEAN
					case Types.BIT:
					case Types.BOOLEAN:
						fieldConfig.append("{id: '" + column_name + "', label: '" + column_caption + "', type: 'string'},");
						break;
					//日期型
					case Types.DATE:
						fieldConfig.append("{id: '" + column_name + "', label: '" + column_caption + "', type: 'date', format: 'Y-m-d H:i:s'},");
						break;
					case Types.TIME:	
						fieldConfig.append("{id: '" + column_name + "', label: '" + column_caption + "', type: 'date', format: 'Y-m-d H:i:s'},");
						break;
					case Types.TIMESTAMP:
						fieldConfig.append("{id: '" + column_name + "', label: '" + column_caption + "', type: 'date', format: 'Y-m-d H:i:s'},");
						break;
					//blob
					case Types.BLOB:
						break;
					//clob
					case Types.CLOB:
						break;
					default:
					}
				}		
				String filterConfig = fieldConfig.toString();
				if(filterConfig.endsWith(",")){
					filterConfig = filterConfig.substring(0, filterConfig.length()-1);
				}
				
				this.json = "[" + filterConfig + "]"; 
			}else if("jsonBuildFilter".equalsIgnoreCase(this.whichJson)){
				JsonFilterResolver filterResolver = new JsonFilterResolver();
				this.json = filterResolver.getSql(JSONObject.fromObject(this.source));				
			}
		}catch(Exception e){
			e.printStackTrace();
		}
 
		return AJAX;
	}

	public Query getQuery() {
		return query;
	}

	public void setQuery(Query query) {
		this.query = query;
	}

	public QueryManager getQueryManager() {
		return queryManager;
	}

	public void setQueryManager(QueryManager queryManager) {
		this.queryManager = queryManager;
	}

	public QueryParameterManager getQueryParameterManager() {
		return queryParameterManager;
	}

	public void setQueryParameterManager(QueryParameterManager queryParameterManager) {
		this.queryParameterManager = queryParameterManager;
	}

	public String getField() {
		return field;
	}

	public void setField(String field) {
		this.field = field;
	}

	public String getValue() {
		return value;
	}

	public void setValue(String value) {
		this.value = value;
	}

	public String getCrud() {
		return crud;
	}

	public void setCrud(String crud) {
		this.crud = crud;
	}

	public String getDeletedQuery() {
		return deletedQuery;
	}

	public void setDeletedQuery(String deletedQuery) {
		this.deletedQuery = deletedQuery;
	}

	public static long getSerialVersionUID() {
		return serialVersionUID;
	}

	public String getJsonQueryParams() {
		return jsonQueryParams;
	}

	public void setJsonQueryParams(String jsonQueryParams) {
		this.jsonQueryParams = jsonQueryParams;
	}

	public String getServiceGuid() {
		return serviceGuid;
	}

	public void setServiceGuid(String serviceGuid) {
		this.serviceGuid = serviceGuid;
	}

	public String getApplicationGuid() {
		return applicationGuid;
	}

	public void setApplicationGuid(String applicationGuid) {
		this.applicationGuid = applicationGuid;
	}

	public String getApplicationName() {
		return applicationName;
	}

	public void setApplicationName(String applicationName) {
		this.applicationName = applicationName;
	}

	public ApplicationManager getApplicationManager() {
		return applicationManager;
	}

	public void setApplicationManager(ApplicationManager applicationManager) {
		this.applicationManager = applicationManager;
	}

	public CatalogManager getCatalogManager() {
		return catalogManager;
	}

	public void setCatalogManager(CatalogManager catalogManager) {
		this.catalogManager = catalogManager;
	}

	public String getTableName() {
		return tableName;
	}

	public void setTableName(String tableName) {
		this.tableName = tableName;
	}

	public DbMetaDataManager getDbMetaDataManager() {
		return dbMetaDataManager;
	}

	public void setDbMetaDataManager(DbMetaDataManager dbMetaDataManager) {
		this.dbMetaDataManager = dbMetaDataManager;
	}

}
